﻿Imports System.Data.SqlServerCe

Public Class MediaInfo
    Shared Sub New()
        Dim aSelectColumns As String() = {"SharedItemId", "FeatureTitle", "Version", "Genres", "OpenMediaInfoXml/OpenMediaInfo"}
        ObjectPersister.DefineObject(GetType(Entities.MediaInfo), "MediaInfo", aSelectColumns)
        ObjectPersister.AddCustomFieldHandler(GetType(Entities.MediaInfo), "Genres", AddressOf Handler_SetGenresField)
        ObjectPersister.AddCustomFieldHandler(GetType(Entities.MediaInfo), "OpenMediaInfo", AddressOf Handler_SetOpenMediaInfoField)
    End Sub
    Private Sub New()
    End Sub

    Public Shared Function GetBySharedItemId(ByVal sharedItemId As Integer) As Entities.MediaInfo
        Return ObjectPersister.GetOne(Of Entities.MediaInfo)(String.Format("SharedItemId={0}", sharedItemId))
    End Function

    Public Shared Function GetAllByShareId(ByVal shareId As Integer) As Generic.List(Of Entities.MediaInfo)
        Return ObjectPersister.GetAll(Of Entities.MediaInfo)(String.Format("shareditemid in (select id from shareditems where sourcewatchfolderid = {0})", shareId))
    End Function

    Public Shared Sub Delete(ByVal sharedItemId As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM MediaInfo WHERE SharedItemId={0}", sharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub

    Private Shared Sub Handler_SetGenresField(ByVal obj As Object, ByVal value As Object)
        If value IsNot Nothing Then
            CType(obj, Entities.MediaInfo).Genres = Utils.Serialization.FromCommaSepString(CStr(value))
        End If
    End Sub
    Private Shared Sub Handler_SetOpenMediaInfoField(ByVal obj As Object, ByVal value As Object)
        If value IsNot Nothing Then
            CType(obj, Entities.MediaInfo).OpenMediaInfo = Utils.Serialization.FromXml(Of Entities.OpenMediaInfo.OpenMediaInfo)(CStr(value))
        End If
    End Sub

    Public Shared Sub Save(ByVal item As Entities.MediaInfo)
        Using t As New TransactionScope
            Save(item, t)
            t.Complete()
        End Using
    End Sub
    Public Shared Sub Save(ByVal item As Entities.MediaInfo, ByVal transactionScope As TransactionScope)
        ValidateItem(item)

        Dim clsCmd As New SaveCommand(Of Integer)("MediaInfo", "SharedItemId", item.SharedItemId)
        clsCmd.AddParam("FeatureTitle", item.FeatureTitle, True)
        clsCmd.AddParam("Version", item.Version, False)
        clsCmd.AddParam("Genres", Utils.Serialization.ToCommaSepString(item.Genres), True)
        If item.OpenMediaInfo IsNot Nothing Then
            clsCmd.AddParam("OpenMediaInfoXml", Utils.Serialization.ToXml(item.OpenMediaInfo), True)
        Else
            clsCmd.AddParam("OpenMediaInfoXml", DBNull.Value, True)
        End If
        clsCmd.Save(transactionScope)

        'fill cast, directors and writers table
        UpdateCast(item, transactionScope)
        UpdateDirectors(item, transactionScope)
        UpdateWriters(item, transactionScope)
        'fill certifications table
        UpdateCertification(item, transactionScope)
    End Sub
    Private Shared Sub UpdateCast(ByVal item As Entities.MediaInfo, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM MediaInfo_Cast WHERE SharedItemId={0}", item.SharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope)

        If item.OpenMediaInfo.Feature IsNot Nothing Then
            If item.OpenMediaInfo.Feature.Cast IsNot Nothing Then
                strSql = "INSERT INTO MediaInfo_Cast (SharedItemId,Name) VALUES (@SharedItemId,@Name)"
                Dim sqlCmd As SqlCeCommand = Environment.NewCommand(strSql, transactionScope)
                sqlCmd.Parameters.AddWithValue("@SharedItemId", item.SharedItemId)
                sqlCmd.Parameters.AddWithValue("@Name", String.Empty)
                For Each clsContributer As Entities.OpenMediaInfo.Contributer In item.OpenMediaInfo.Feature.Cast
                    sqlCmd.Parameters("@Name").Value = clsContributer.Name
                    Environment.ExecuteNonQuery(sqlCmd)
                Next
            End If
        End If
    End Sub
    Private Shared Sub UpdateDirectors(ByVal item As Entities.MediaInfo, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM MediaInfo_Directors WHERE SharedItemId={0}", item.SharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope)

        If item.OpenMediaInfo.Feature IsNot Nothing Then
            If item.OpenMediaInfo.Feature.Cast IsNot Nothing Then
                strSql = "INSERT INTO MediaInfo_Directors (SharedItemId,Name) VALUES (@SharedItemId,@Name)"
                Dim sqlCmd As SqlCeCommand = Environment.NewCommand(strSql, transactionScope)
                sqlCmd.Parameters.AddWithValue("@SharedItemId", item.SharedItemId)
                sqlCmd.Parameters.AddWithValue("@Name", String.Empty)
                For Each clsContributer As Entities.OpenMediaInfo.Contributer In item.OpenMediaInfo.Feature.Directors
                    sqlCmd.Parameters("@Name").Value = clsContributer.Name
                    Environment.ExecuteNonQuery(sqlCmd)
                Next
            End If
        End If
    End Sub
    Private Shared Sub UpdateWriters(ByVal item As Entities.MediaInfo, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM MediaInfo_Writers WHERE SharedItemId={0}", item.SharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope)

        If item.OpenMediaInfo.Feature IsNot Nothing Then
            If item.OpenMediaInfo.Feature.Cast IsNot Nothing Then
                strSql = "INSERT INTO MediaInfo_Writers (SharedItemId,Name) VALUES (@SharedItemId,@Name)"
                Dim sqlCmd As SqlCeCommand = Environment.NewCommand(strSql, transactionScope)
                sqlCmd.Parameters.AddWithValue("@SharedItemId", item.SharedItemId)
                sqlCmd.Parameters.AddWithValue("@Name", String.Empty)
                For Each clsContributer As Entities.OpenMediaInfo.Contributer In item.OpenMediaInfo.Feature.Writers
                    sqlCmd.Parameters("@Name").Value = clsContributer.Name
                    Environment.ExecuteNonQuery(sqlCmd)
                Next
            End If
        End If
    End Sub

    Private Shared Sub UpdateCertification(ByVal item As Entities.MediaInfo, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM MediaInfo_Certifications WHERE SharedItemId={0}", item.SharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope)

        If item.OpenMediaInfo.Feature IsNot Nothing Then
            If item.OpenMediaInfo.Feature.Certifications IsNot Nothing Then
                strSql = "INSERT INTO MediaInfo_Certifications (SharedItemId,Country,Level,Note) VALUES (@SharedItemId,@Country,@Level,@Note)"
                Dim sqlCmd As SqlCeCommand = Environment.NewCommand(strSql, transactionScope)
                sqlCmd.Parameters.AddWithValue("@SharedItemId", item.SharedItemId)
                sqlCmd.Parameters.AddWithValue("@Country", String.Empty)
                sqlCmd.Parameters.AddWithValue("@Level", String.Empty)
                sqlCmd.Parameters.AddWithValue("@Note", String.Empty)
                For Each clsActor As Entities.OpenMediaInfo.Certification In item.OpenMediaInfo.Feature.Certifications
                    sqlCmd.Parameters("@Country").Value = clsActor.Country
                    sqlCmd.Parameters("@Level").Value = clsActor.Level
                    sqlCmd.Parameters("@Note").Value = IIf(String.IsNullOrEmpty(clsActor.Note), DBNull.Value, clsActor.Note)
                    Environment.ExecuteNonQuery(sqlCmd)
                Next
            End If
        End If
    End Sub

    Private Shared Sub ValidateItem(ByVal item As Entities.MediaInfo)
        If item.SharedItemId < 1 Then Throw New ArgumentException("SharedItemId not valid!")
    End Sub

End Class
